package com.jingdun.sport.authority

import com.jingdun.sport.data.environment.select
import com.losttemple.sql.language.operator.*
import com.losttemple.sql.language.types.SetRef
import com.losttemple.sql.language.types.SqlType

class UserRoles(set: (TableConfigure.() -> Unit) -> SetRef) :
        DbSource(set { tableName("user_roles") }) {
    var id = intColumn("id")
    var user = stringColumn("user")
    var role = intColumn("role")
}

class Roles(set: (TableConfigure.() -> Unit) -> SetRef) :
        DbSource(set { tableName("roles") }) {
    var id = intColumn("id")
    var name = stringColumn("name")
}

class Authorities(set: (TableConfigure.() -> Unit) -> SetRef) :
        DbSource(set { tableName("authorities") }) {
    var id = intColumn("id")
    var name = stringColumn("name")
}

class RoleAuthorities(set: (TableConfigure.() -> Unit) -> SetRef) :
        DbSource(set { tableName("role_authorities") }) {
    var id = intColumn("id")
    var role = intColumn("role")
    var authority = intColumn("authority")
}

class Users(set: (TableConfigure.() -> Unit) -> SetRef) :
        DbSource(set { tableName("tz_user") }) {
    var id = stringColumn("user_id")
    var authentic = boolColumn("authentic")
    var money = doubleColumn("money")
    var realName = stringColumn("real_name")
    var sex = stringColumn("sex")
    var birthDate = stringColumn("birth_date")
}

data class UsersAndRoles(
        val users: SqlType<String>,
        val roles: Roles
)

data class UsersAndAuthorities(
        val users: SqlType<String>,
        val roles: Roles,
        val authorities: Authorities
)

fun fromUserAndRole(): DbSet<UsersAndRoles> {
    return (from { UserRoles(it) } innerJoin from { Roles(it) }).select {
        object {
            val userRoles = left
            val roles = right
        }
    }.on {
        userRoles.role eq roles.id
    }.select { UsersAndRoles(userRoles.user, roles) }
}

fun fromUserAndAuthorities(): DbSet<UsersAndAuthorities> {
    return (fromUserAndRole() innerJoin from { RoleAuthorities(it) }).select {
        object {
            val userRoles = left
            val roleAuthorities = right
        }
    }.on { userRoles.roles.id eq roleAuthorities.role }.innerJoin(from { Authorities(it) }).select {
        object {
            val users = left.userRoles.users
            val roles = left.userRoles.roles
            val roleAuthorities = left.roleAuthorities
            val authorities = right
        }
    }.on { roleAuthorities.authority eq authorities.id }.select { UsersAndAuthorities(users, roles, authorities) }
}